﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Configuration;

namespace Talentnet.Core.Database
{
    public class SQLProvider
    {
        public string ConnectionString { get; set; }
        
        SqlDatabase _db;
        DbCommand _com;
        public SQLProvider(string key)
        {
            if (ConfigurationManager.ConnectionStrings[key] == null) 
                throw new Exception("Invalid connection key");
            ConnectionString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
            _db = new SqlDatabase(ConnectionString);
        }

        public SQLProvider()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
            _db = new SqlDatabase(ConnectionString);
        }

        public void Dispose()
        {
            if (_com != null)
            {
                _com.Dispose();
                _com = null;
            }
            if (_db != null)
            {
                _db = null;
            }
        }

        private void CreateProcCommand(string proc)
        {
            if (_com != null)
            {
                _com.Dispose();
                _com = null;
            }
            _com = _db.GetStoredProcCommand(proc);
        }
        private void CreateProcCommand(string proc, params object[] paramsList)
        {
            if (_com != null)
            {
                _com.Dispose();
                _com = null;
            }
            _com = _db.GetStoredProcCommand(proc, paramsList);
        }
        public object GetLastOutPutPararamValue(string name)
        {
            return _db.GetParameterValue(_com, name);
        }
        public IDataReader ExecReader(string proc, List<SqlParameter> paramsList)
        {

            CreateProcCommand(proc);
            foreach (SqlParameter param in paramsList)
            {
                switch (param.Direction)
                {
                    case ParameterDirection.Input:
                    case ParameterDirection.InputOutput:
                        _db.AddInParameter(_com, param.ParameterName, param.SqlDbType, param.Value);
                        break;
                }
            }
            
            return _db.ExecuteReader(_com);
        }
        public DataTable[] ExecDataTables(string proc, List<SqlParameter> paramsList)
        {

            CreateProcCommand(proc);
            foreach (SqlParameter param in paramsList)
            {
                switch (param.Direction)
                {
                    case ParameterDirection.Input:
                    case ParameterDirection.InputOutput:
                        _db.AddInParameter(_com, param.ParameterName, param.SqlDbType, param.Value);
                        break;
                }
            }

            DataSet ds = _db.ExecuteDataSet(_com);
            return ds.Tables.Cast<DataTable>().ToArray<DataTable>();
        }
        public DataTable[] ExecDataTables(string proc, params object[] paramsList)
        {
            CreateProcCommand(proc, paramsList);
            DataSet ds = _db.ExecuteDataSet(_com);
            return ds.Tables.Cast<DataTable>().ToArray<DataTable>();
        }
        public DataTable ExecDataTable(string proc, List<SqlParameter> paramsList)
        {
            CreateProcCommand(proc);
            foreach (SqlParameter param in paramsList)
            {
                switch (param.Direction)
                {
                    case ParameterDirection.Input:
                    case ParameterDirection.InputOutput:
                        _db.AddInParameter(_com, param.ParameterName, param.SqlDbType, param.Value);
                        break;
                }
            }

            DataSet ds = _db.ExecuteDataSet(_com);
            return ds.Tables[0];
        }
        public DataTable ExecDataTable(string proc, params object[] paramsList)
        {
            CreateProcCommand(proc, paramsList);
            DataSet ds = _db.ExecuteDataSet(_com);
            return ds.Tables[0];
        }
        public IDataReader ExecReader(string proc, params object[] paramsList)
        {
            CreateProcCommand(proc,paramsList);
            return _db.ExecuteReader(_com);
        }

        public int ExecCommand(string proc, List<SqlParameter> paramsList)
        {
            CreateProcCommand(proc);
            foreach (SqlParameter param in paramsList)
            {
                switch (param.Direction)
                {
                    case ParameterDirection.Input:
                    case ParameterDirection.InputOutput:
                        _db.AddInParameter(_com, param.ParameterName, param.SqlDbType, param.Value);
                        break;
                }
            }
            return _db.ExecuteNonQuery(_com);
        }

        public int ExecCommand(string proc, params object[] paramsList)
        {
            CreateProcCommand(proc, paramsList);
            return _db.ExecuteNonQuery(_com);
        }
    }
}
